/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.List;
import javax.swing.JOptionPane;
import model.Classe;
import model.Familia;
import model.Material;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import util.HibernateUtil;

/**
 *
 * @author JONATAS
 */
public class MaterialDAO {

    private Session session;

    // método construtor - igual ao nome da Material precedido por public
    public MaterialDAO() {
        session = HibernateUtil.getSessionFactory().openSession();
    }

    public void insert(Material m) {
        Transaction t = session.beginTransaction();
        session.save(m);
    }

    public void update(Material m) {
        Transaction t = session.beginTransaction();
        session.merge(m);
        t.commit();
    }

    public void delete(Material m) {
        Transaction t = session.beginTransaction();
        session.delete(m);
        t.commit();
    }

    public Material findById(String id) {
        return (Material) session.load(Material.class, id);
    }

    public Material findByIdComposta(String id) {

        Query q = session.createQuery("from Material where mat_id = :id");
        q.setParameter("id", id);
        return (Material) q.uniqueResult();
    }

    public List<Material> findAll() {
        return session.createCriteria(Material.class).list(); // faz um select * from marca
    }

    public List<Material> findByDescricao(String descricao) {

        Query q = session.createQuery("from Material where upper(mat_descricao) like upper(:X)");

        q.setParameter("X", "%" + descricao + "%");
        return q.list();

    }
    public List<Material> findMateriasPrimas() {

        Query q = session.createQuery("Select mat from Material as mat inner join mat.mat_familia as fm inner join fm.fam_classe as cl where cl.produtoIntermediario = 0");         
        return q.list();        
    }
    public List<Material> findMateriasPrimasByDescricao(String descricao) {

        Query q = session.createQuery("Select mat from Material as mat inner join mat.mat_familia as fm inner join fm.fam_classe as cl where cl.produtoIntermediario = 0 and  upper(mat_descricao) like upper(:X)"); 
        q.setParameter("X", "%" + descricao + "%");         
        return q.list();        
    }
    
    public List<Material> findByClasse(int classe_id) {
        System.out.println("Find by classe --- " + classe_id);
        Query q = session.createQuery("from Material where cla_id = :X");
        q.setParameter("X", "" + classe_id);
        return q.list();
    }

    public List<Material> findComplete(String codigo, String descricao, Classe classe, Familia familia) {
        
        String sql = " from Material where mat_id like :matid and mat_descricao like :descr ";
        Query q = session.createQuery(sql);
        //Query q = session.createQuery("from Material where mat_id like :matid and mat_descricao like :descr");        
        q.setParameter("matid", "%" + codigo + "%");
        q.setParameter("descr", "%" + descricao + "%");
        
        if (familia.getFam_id() != null) {            
            
            sql = sql+" and mat_familia = :familia "; 
            q = session.createQuery(sql);
            q.setParameter("familia", familia);
            q.setParameter("matid", "%" + codigo + "%");
            q.setParameter("descr", "%" + descricao + "%");            
        }                                       
        else if(classe.getCla_id() != null){
                sql = sql+" and cast(substring(mat_id, 1, 2) as integer) = :classe";
                q = session.createQuery(sql);                
                q.setParameter("classe", Integer.parseInt(classe.getCla_id()));
                q.setParameter("matid", "%" + codigo + "%");
                q.setParameter("descr", "%" + descricao + "%");            
            }
        System.out.println("HQL -> "+sql);
        
        return q.list();
    }

    public String getCode(String fam_id) {
        try {
            Query q = session.createSQLQuery("Select max(lpad(cast(mat_identificador as int)+1,7,0)) from Material Where fam_id = :FAMID");
            q.setParameter("FAMID", fam_id);
            if (Integer.parseInt(q.uniqueResult().toString()) == 99999999) {
                JOptionPane.showMessageDialog(null, "Limite de cadastro excedido!");
                return "ERRO!";
            }
            System.out.println("Results -> " + q.uniqueResult().toString());
            return q.uniqueResult().toString();
        } catch (org.hibernate.exception.GenericJDBCException ex) {
            System.out.println("CATCH NULL");
            return "0000001";
        }
        catch(java.lang.NullPointerException ex){
        System.out.println("CATCH NULL");
            return "0000001";
        }

    }

    public void Commit() {
        Transaction t = session.beginTransaction();
        t.commit();
    }
}
